package jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class PaginationTest {
	public static void main(String[] args) throws Exception {
	}
	
	private static void rownum(int page,int pageSize) {
		//要给hh_stu表取别名才能使用
		String sql="select * from (select s.* ,rownum r from hh_stu s where rownum<5) where r>=2";
		int start=(page-1)*pageSize+1;
		int end=start+pageSize;
		
	}
	
	private static void rsabosoluteTest() throws Exception{
		rsabsolute(1,10);
		System.out.println("********");
		rsabsolute(1,3);
		System.out.println("********");
		rsabsolute(2,3);
		System.out.println("********");
		rsabsolute(3, 3);
		
	}
	//可滚动的结果集效率较rownum低，即数据库提供的方法
	private static void rsabsolute(int page,int pageSize) throws Exception{
		String sql="select * from s_emp";
		Connection con=null;
		Statement stm=null;
		ResultSet rs =null;
		try{
			con=DBUtil.open();
			stm=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = stm.executeQuery(sql);
			int row = (page-1)*pageSize+1;
			//指针定位到指定行
			//超出数据行数会定位到最后一行之后
			rs.absolute(row);
			//定位在前后两个特殊位置
			if(rs.isAfterLast()||rs.isBeforeFirst()){
				return;
			}
			for(int i=0;i<pageSize;i++){
				int id= rs.getInt("id");
				String name = rs.getString("last_name");
				System.out.println(id+","+name);
				if(!rs.next())
					break;
			}
		}finally{
			DBUtil.close(con, stm, rs);
		}
	}
}
